package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;

import co.gov.medellin.ssti.sub.model.dao.ProgramaDAO;
import co.gov.medellin.ssti.sub.model.vo.ProgramaVO;

public class ProgramaDAOImpl2 extends NamedParameterJdbcDaoSupport implements
		ProgramaDAO {

	// **************************************
	// QUERYS
	// **************************************

	private static final String SELECT_TODOS_PROGRAMAS_VIGENTES = "SELECT P.CODIGO CODIGO, "
			+ " P.AREA_CODIGO AREA, P.NOMBRE NOMBRE, P.DESCRIPCION DESCRIPCION, "
			+ " P.USU_CODIGO USU_CODIGO, P.VIGENCIA VIGENCIA FROM TCS_BEN_PROGRAMA P "
			+ " WHERE P.VIGENCIA = 'A' ORDER BY NOMBRE ";

	private static final String SELECT_TODOS_PROGRAMAS_BY_AREA = "SELECT P.CODIGO CODIGO, "
			+ " P.AREA_CODIGO AREA, P.NOMBRE NOMBRE, P.DESCRIPCION DESCRIPCION, "
			+ " P.USU_CODIGO USU_CODIGO, P.VIGENCIA VIGENCIA FROM TCS_BEN_PROGRAMA P "
			+ " WHERE P.VIGENCIA = 'A' AND P.AREA_CODIGO = :AREA_CODIGO ORDER BY NOMBRE ";

	public List<ProgramaVO> loadAllProgramas() {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				SELECT_TODOS_PROGRAMAS_VIGENTES, namedParameters,
				new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						ProgramaVO vo = new ProgramaVO();
						vo.setCode(new Long(rs.getLong("CODIGO")));
						vo.setNombre(rs.getString("NOMBRE"));
						return vo;
					}
				});
		return programas;
	}

	public List<ProgramaVO> loadAllProgramasByArea(String secretaryCode) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		namedParameters.put("AREA_CODIGO", secretaryCode);
		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				SELECT_TODOS_PROGRAMAS_BY_AREA, namedParameters,
				new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						ProgramaVO vo = new ProgramaVO();
						vo.setCode(new Long(rs.getLong("CODIGO")));
						vo.setNombre(rs.getString("NOMBRE"));
						return vo;
					}
				});
		return programas;
	}

	public Collection<ProgramaVO> findProgramaByUserReport(Long codeUser,
			String userGroupName, Long codeArea) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		ProgramaVO criteria = new ProgramaVO();
		String query = "SELECT DISTINCT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, pro.DESCRIPCION DESC_PROGRAMA, "
				+ " pro.OBJETIVO, pro.POBLACION_OBJETIVO, area.CODIGO COD_AREA, area.NOMBRE NOM_AREA, "
				+ " usu.CODIGO COD_USUARIO, usu.NOMBRE NOM_USUARIO, a.admProg "
				+ " FROM VCS_BEN_SUBPROG_ALLOWED a, "
				+ " TCS_BEN_PROGRAMA pro, TCS_BEN_AREA area, paoyer.TCS_USUARIO usu, "
				+ " paoyer.tcs_grupo_usuario gus, TCS_BEN_SUBPROGRAMA_USUARIO subus "
				+ " WHERE  gus.CODIGO = usu.GUS_CODIGO "
				+ " AND a.COD_PROGRAMA = pro.CODIGO "
				+ " AND pro.AREA_CODIGO = area.CODIGO "
				+ " AND pro.USU_CODIGO = usu.CODIGO "
				+ " AND pro.VIGENCIA = 'A' ";

		if ((Object) codeUser != null) {
			query += " AND ('ADMINISTRADOR GENERAL' = :userGroupName "
					+ " OR 'SUPER USUARIO' = :userGroupName OR admSubpro = :codeUser "
					+ " OR pro.USU_CODIGO = :codeUser OR a.permisos = :codeUser ";
			namedParameters.put("userGroupName", userGroupName);
			namedParameters.put("codeUser", codeUser);

			if (userGroupName != null
					&& userGroupName.equals("ADMINISTRADOR SECRETARIA")) {
				query += " OR COD_AREA = :COD_AREA ";
				namedParameters.put("COD_AREA", codeArea);
			}
			query += " ) ";
		} else {
			return null;
		}

		if (criteria.getCode() != null) {
			query += "AND COD_PROGRAMA = :COD_PROGRAMA ";
			namedParameters.put("COD_PROGRAMA", criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query += "AND NOM_PROGRAMA LIKE :NOM_PROGRAMA ";
			namedParameters.put("NOM_PROGRAMA", "%"
					+ criteria.getNombre().toUpperCase().trim() + "%");
		}
		if (criteria.getCodeArea() != null) {
			query += "AND COD_AREA = :COD_AREA ";
			namedParameters.put("COD_AREA", criteria.getCodeArea());
		}

		query += " ORDER BY NOM_PROGRAMA";
		// System.out.println("query: " + query);

		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				query, namedParameters, new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						ProgramaVO vo = new ProgramaVO();
						vo.setCode(new Long(rs.getLong("COD_PROGRAMA")));
						vo.setNombre(rs.getString("NOM_PROGRAMA"));
						vo.setDescripcion(rs.getString("DESC_PROGRAMA"));
						vo.setObjetivo(rs.getString("OBJETIVO"));
						vo.setPoblacionObjetivo(rs
								.getString("POBLACION_OBJETIVO"));
						vo.setCodeArea(new Long(rs.getString("COD_AREA")));
						vo.setNombreArea(rs.getString("NOM_AREA"));
						vo.setCodeUsuario(new Long(rs.getString("COD_USUARIO")));
						vo.setNombreUsuario(rs.getString("NOM_USUARIO"));
						return vo;
					}
				});
		return programas;
	}

	public Collection<ProgramaVO> findProgramaByCriteria(ProgramaVO criteria) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		StringBuffer query = new StringBuffer(
				"SELECT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, "
						+ " pro.DESCRIPCION DESC_PROGRAMA, pro.OBJETIVO, "
						+ " pro.POBLACION_OBJETIVO, area.CODIGO COD_AREA, area.NOMBRE NOM_AREA, "
						+ " usu.CODIGO COD_USUARIO, usu.NOMBRE NOM_USUARIO "
						+ " FROM TCS_BEN_PROGRAMA pro, "
						+ " TCS_BEN_AREA area, "
						+ " paoyer.TCS_USUARIO usu "
						+ " WHERE Fcs_Ben_Val_Vigencia(pro.VIGENCIA, pro.DESDE_VIGENCIA, pro.HASTA_VIGENCIA, sysdate) = 'A' "
						+ " AND pro.AREA_CODIGO = area.CODIGO "
						+ " AND pro.USU_CODIGO = usu.CODIGO ");

		if (criteria.getCode() != null) {
			query.append(" AND pro.CODIGO = :pro.CODIGO ");
			namedParameters.put("pro.CODIGO", criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND pro.NOMBRE LIKE :NOMBRE ");
			namedParameters.put("NOMBRE", "%"
					+ criteria.getNombre().toUpperCase().trim() + "%");
		}
		if (criteria.getCodeArea() != null) {
			query.append(" AND area.CODIGO = :area.CODIGO ");
			namedParameters.put("area.CODIGO", criteria.getCodeArea());
		}

		query.append(" ORDER BY pro.NOMBRE");

		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				query.toString(), namedParameters, new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						ProgramaVO pro = new ProgramaVO();
						pro.setCode(new Long(rs.getLong("COD_PROGRAMA")));
						pro.setNombre(rs.getString("NOM_PROGRAMA"));
						pro.setDescripcion(rs.getString("DESC_PROGRAMA"));
						pro.setObjetivo(rs.getString("OBJETIVO"));
						pro.setPoblacionObjetivo(rs
								.getString("POBLACION_OBJETIVO"));
						pro.setCodeArea(new Long(rs.getString("COD_AREA")));
						pro.setNombreArea(rs.getString("NOM_AREA"));
						pro.setCodeUsuario(new Long(rs.getString("COD_USUARIO")));
						pro.setNombreUsuario(rs.getString("NOM_USUARIO"));
						return pro;
					}
				});
		return programas;
	}

	public Collection<ProgramaVO> findAllProgramaByCriteria(ProgramaVO criteria) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		StringBuffer query = new StringBuffer(
				"SELECT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, "
						+ " pro.DESCRIPCION DESC_PROGRAMA, pro.OBJETIVO, "
						+ " pro.POBLACION_OBJETIVO, area.CODIGO COD_AREA, area.NOMBRE NOM_AREA, "
						+ " usu.CODIGO COD_USUARIO, usu.NOMBRE NOM_USUARIO "
						+ " FROM TCS_BEN_PROGRAMA pro, "
						+ " TCS_BEN_AREA area, paoyer.TCS_USUARIO usu "
						+ " WHERE pro.AREA_CODIGO = area.CODIGO "
						+ " AND pro.USU_CODIGO = usu.CODIGO");

		if (criteria.getCode() != null) {
			query.append(" AND pro.CODIGO = :pro.CODIGO ");
			namedParameters.put("pro.CODIGO", criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND pro.NOMBRE LIKE :NOMBRE ");
			namedParameters.put("NOMBRE", "%"
					+ criteria.getNombre().toUpperCase().trim() + "%");
		}
		if (criteria.getCodeArea() != null) {
			query.append(" AND area.CODIGO = :area.CODIGO ");
			namedParameters.put("area.CODIGO", criteria.getCodeArea());
		}

		query.append(" ORDER BY pro.NOMBRE");

		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				query.toString(), namedParameters, new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						ProgramaVO pro = new ProgramaVO();
						pro.setCode(new Long(rs.getLong("COD_PROGRAMA")));
						pro.setNombre(rs.getString("NOM_PROGRAMA"));
						pro.setDescripcion(rs.getString("DESC_PROGRAMA"));
						pro.setObjetivo(rs.getString("OBJETIVO"));
						pro.setPoblacionObjetivo(rs
								.getString("POBLACION_OBJETIVO"));
						pro.setCodeArea(new Long(rs.getString("COD_AREA")));
						pro.setNombreArea(rs.getString("NOM_AREA"));
						pro.setCodeUsuario(new Long(rs.getString("COD_USUARIO")));
						pro.setNombreUsuario(rs.getString("NOM_USUARIO"));
						return pro;
					}
				});
		return programas;
	}

	public Collection<ProgramaVO> findProgramaByCriteria(ProgramaVO criteria,
			Long codeUser, String groupUserName, Long areaUser) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		StringBuffer query = new StringBuffer(
				"SELECT DISTINCT COD_PROGRAMA, NOM_PROGRAMA, DESC_PROGRAMA, OBJETIVO, POBLACION_OBJETIVO, COD_AREA, NOM_AREA, COD_USUARIO, NOM_USUARIO  from VCS_BEN_PROGRAMAS  ");

		if ((Object) codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = :groupUserName "
					+ " OR 'SUPER USUARIO' = :groupUserName OR admSubpro = :codeUser "
					+ "          OR admProg = :codeUser ");
			namedParameters.put("groupUserName", groupUserName);
			namedParameters.put("codeUser", codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = :COD_AREA ");
				namedParameters.put("COD_AREA", areaUser);
			}
			query.append(" ) ");
		} else {
			return null;
		}

		if (criteria.getCode() != null) {
			query.append(" AND COD_PROGRAMA = :COD_PROGRAMA ");
			namedParameters.put("COD_PROGRAMA", criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND NOM_PROGRAMA LIKE :NOM_PROGRAMA ");
			namedParameters.put("NOM_PROGRAMA", "%"
					+ criteria.getNombre().toUpperCase().trim() + "%");
		}
		if (criteria.getCodeArea() != null) {
			query.append(" AND COD_AREA = :COD_AREA ");
			namedParameters.put("COD_AREA", criteria.getCodeArea());
		}

		query.append(" ORDER BY NOM_PROGRAMA");
		System.out.println(query);
		for (String s : namedParameters.keySet()) {
			System.out.println(s + ":" + namedParameters.get(s));
		}

		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				query.toString(), namedParameters, new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rst, int rowNum)
							throws SQLException {
						ProgramaVO pro = new ProgramaVO();
						pro.setCode(new Long(rst.getLong("COD_PROGRAMA")));
						pro.setNombre(rst.getString("NOM_PROGRAMA"));
						pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
						pro.setObjetivo(rst.getString("OBJETIVO"));
						pro.setPoblacionObjetivo(rst
								.getString("POBLACION_OBJETIVO"));
						pro.setCodeArea(new Long(rst.getString("COD_AREA")));
						pro.setNombreArea(rst.getString("NOM_AREA"));
						pro.setCodeUsuario(new Long(rst
								.getString("COD_USUARIO")));
						pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
						return pro;
					}
				});
		return programas;
	}

	public Collection<ProgramaVO> findProgramaByCriteriaOB(ProgramaVO criteria,
			Long codeUser, String groupUserName, Long areaUser) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		StringBuffer query = new StringBuffer(
				"SELECT DISTINCT COD_PROGRAMA, NOM_PROGRAMA, DESC_PROGRAMA, OBJETIVO, POBLACION_OBJETIVO, COD_AREA, NOM_AREA, COD_USUARIO, NOM_USUARIO  from VCS_BEN_PROGRAMAS ");

		if ((Object) codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = :groupUserName "
					+ " OR 'SUPER USUARIO' = :groupUserName OR admSubpro = :admSubpro "
					+ "          OR admProg = :admSubpro OR ususede = :admSubpro ");
			namedParameters.put("groupUserName", groupUserName);
			namedParameters.put("admSubpro", codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = :COD_AREA ");
				namedParameters.put("COD_AREA", areaUser);
			}
			query.append(" ) ");
		} else {
			return null;
		}

		if (criteria.getCode() != null) {
			query.append(" AND COD_PROGRAMA = :COD_PROGRAMA ");
			namedParameters.put("COD_PROGRAMA", criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND NOM_PROGRAMA LIKE :NOM_PROGRAMA ");
			namedParameters.put("NOM_PROGRAMA", "%"
					+ criteria.getNombre().toUpperCase().trim() + "%");
		}
		if (criteria.getCodeArea() != null) {
			query.append(" AND COD_AREA = :COD_AREA ");
			namedParameters.put("COD_AREA", criteria.getCodeArea());
		}

		query.append(" ORDER BY NOM_PROGRAMA");

		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				query.toString(), namedParameters, new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rst, int rowNum)
							throws SQLException {
						ProgramaVO pro = new ProgramaVO();
						pro.setCode(new Long(rst.getLong("COD_PROGRAMA")));
						pro.setNombre(rst.getString("NOM_PROGRAMA"));
						pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
						pro.setObjetivo(rst.getString("OBJETIVO"));
						pro.setPoblacionObjetivo(rst
								.getString("POBLACION_OBJETIVO"));
						pro.setCodeArea(new Long(rst.getString("COD_AREA")));
						pro.setNombreArea(rst.getString("NOM_AREA"));
						pro.setCodeUsuario(new Long(rst
								.getString("COD_USUARIO")));
						pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
						return pro;
					}
				});
		return programas;
	}

	public Collection<ProgramaVO> findProgramaWithoutSede(ProgramaVO criteria,
			Long codeUser, String groupUserName, Long areaUser) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		StringBuffer query = new StringBuffer(
				"SELECT DISTINCT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, "
						+ " pro.DESCRIPCION DESC_PROGRAMA, pro.OBJETIVO, pro.POBLACION_OBJETIVO, "
						+ " area.CODIGO COD_AREA, area.NOMBRE NOM_AREA,	usu.CODIGO COD_USUARIO, "
						+ " usu.NOMBRE NOM_USUARIO, a.admProg "
						+ " FROM VCS_BEN_SUBPROG_WITHOUT_SEDE a, TCS_BEN_PROGRAMA pro, "
						+ " TCS_BEN_AREA area, paoyer.TCS_USUARIO usu, paoyer.tcs_grupo_usuario gus "
						+ " WHERE  gus.CODIGO = usu.GUS_CODIGO "
						+ " AND a.COD_PROGRAMA = pro.CODIGO "
						+ " AND Fcs_Ben_Val_Vigencia(pro.VIGENCIA, pro.DESDE_VIGENCIA, pro.HASTA_VIGENCIA, SYSDATE) = 'A' "
						+ " AND pro.AREA_CODIGO = area.CODIGO "
						+ " AND pro.USU_CODIGO = usu.CODIGO ");

		if ((Object) codeUser != null) {
			query.append(" AND ('ADMINISTRADOR GENERAL' = :groupUserName "
					+ " OR 'SUPER USUARIO' = :groupUserName OR admSubpro = :codeUser "
					+ "          OR pro.USU_CODIGO = :codeUser ");

			namedParameters.put("groupUserName", groupUserName);
			namedParameters.put("codeUser", codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR area.Codigo = :area.Codigo ");
				namedParameters.put("area.Codigo", areaUser);
			}
			query.append(" ) ");
		} else {
			return null;
		}

		if (criteria.getCode() != null) {
			query.append("AND COD_PROGRAMA = :COD_PROGRAMA ");
			namedParameters.put("COD_PROGRAMA", criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append("AND NOM_PROGRAMA LIKE :NOM_PROGRAMA ");
			namedParameters.put("NOM_PROGRAMA", "%"
					+ criteria.getNombre().toUpperCase().trim() + "%");
		}
		if (criteria.getCodeArea() != null) {
			query.append("AND COD_AREA = :COD_AREA ");
			namedParameters.put("COD_AREA", criteria.getCodeArea());
		}

		query.append(" ORDER BY NOM_PROGRAMA");

		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				query.toString(), namedParameters, new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rst, int rowNum)
							throws SQLException {
						ProgramaVO pro = new ProgramaVO();
						pro.setCode(new Long(rst.getLong("COD_PROGRAMA")));
						pro.setNombre(rst.getString("NOM_PROGRAMA"));
						pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
						pro.setObjetivo(rst.getString("OBJETIVO"));
						pro.setPoblacionObjetivo(rst
								.getString("POBLACION_OBJETIVO"));
						pro.setCodeArea(new Long(rst.getString("COD_AREA")));
						pro.setNombreArea(rst.getString("NOM_AREA"));
						pro.setCodeUsuario(new Long(rst
								.getString("COD_USUARIO")));
						pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
						return pro;
					}
				});
		return programas;
	}

	public Collection<ProgramaVO> findProgramaByCriteria(ProgramaVO criteria,
			String groupUserName, Long codeUser, Long areaUser) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		StringBuffer query = new StringBuffer(
				"SELECT  UPPER(NOM_PROGRAMA) Programa, COD_PROGRAMA Codigo, "
						+ " COD_AREA codArea, NOM_AREA nombreArea "
						+ " FROM VCS_BEN_SOLO_PROGRAMAS_EST_VIG ");

		if (!groupUserName.equalsIgnoreCase("") && codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = :groupUserName "
					+ " OR 'SUPER USUARIO' = :groupUserName "
					+ "          OR COD_USUARIO = :codeUser ");

			namedParameters.put("groupUserName", groupUserName);
			namedParameters.put("codeUser", codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" or COD_AREA = :COD_AREA ");
				namedParameters.put("COD_AREA", areaUser);
			}
			query.append(" ) ");
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND NOM_PROGRAMA LIKE :NOM_PROGRAMA ");
			namedParameters.put("NOM_PROGRAMA", "%"
					+ criteria.getNombre().toUpperCase().trim() + "%");
		}

		if (criteria.getCodePrograma() != null
				&& !criteria.getCodePrograma().equalsIgnoreCase("")) {
			query.append(" AND COD_PROGRAMA = :COD_PROGRAMA ");
			namedParameters.put("COD_PROGRAMA", criteria.getCodePrograma()
					.trim());
		}

		if (criteria.getCodeArea() != null) {
			query.append(" AND COD_AREA = :COD_AREA ");
			namedParameters.put("COD_AREA", criteria.getCodeArea());
		}

		if (criteria.getVigencia() != null) {
			query.append(" AND (vigencia = :vigencia OR sysdate > HASTA_VIGENCIA) ");
			namedParameters.put("vigencia", criteria.getVigencia());
		}

		query.append(" ORDER BY NOM_PROGRAMA");

		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				query.toString(), namedParameters, new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rst, int rowNum)
							throws SQLException {
						ProgramaVO pro = new ProgramaVO();
						pro.setCode(new Long(rst.getLong("CODIGO")));
						pro.setNombre(rst.getString("PROGRAMA"));
						pro.setCodeArea(new Long(rst.getString("CODAREA")));
						pro.setNombreArea(rst.getString("NombreAREA"));
						return pro;
					}
				});
		return programas;
	}

	public Collection<ProgramaVO> findProgramaByCriteriaReport(
			ProgramaVO criteria, String groupUserName, Long codeUser,
			Long areaUser) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		StringBuffer query = new StringBuffer(
				"SELECT  UPPER(NOM_PROGRAMA) Programa, COD_PROGRAMA Codigo, "
						+ " COD_AREA codArea, NOM_AREA nombreArea "
						+ " FROM VCS_BEN_SOLO_PROGRAMAS_EST_VIG v ");

		if (!groupUserName.equalsIgnoreCase("") && codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = :groupUserName "
					+ " OR 'SUPER USUARIO' = :groupUserName "
					+ " OR COD_PROGRAMA=any(SELECT pro.codigo "
					+ " FROM TCS_BEN_PROGRAMA pro "
					+ " WHERE pro.usu_codigo = :codeUser "
					+ " UNION SELECT sub.prg_codigo "
					+ " FROM tcs_ben_subprograma sub "
					+ " WHERE sub.usu_codigo = :codeUser "
					+ " UNION SELECT sub.prg_codigo "
					+ " FROM tcs_ben_subprograma sub, tcs_ben_subprograma_usuario subus "
					+ " WHERE sub.CODIGO = subus.SBPR_CODIGO "
					+ " AND subus.usu_codigo = :codeUser )");
			namedParameters.put("groupUserName", groupUserName);
			namedParameters.put("codeUser", codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = :COD_AREA ");
				namedParameters.put("COD_AREA", areaUser);
			}
			query.append(" ) ");
		}
		/*
		 * if(!criterea.getNombre().equalsIgnoreCase("")) { query +=
		 * " AND NOM_PROGRAMA LIKE '%"
		 * +criterea.getNombre().toUpperCase().trim()+"%' "; }
		 */

		/*
		 * if(criterea.getCodePrograma() != null &&
		 * !criterea.getCodePrograma().equalsIgnoreCase("")) { query +=
		 * " AND COD_PROGRAMA = "+criterea.getCodePrograma().trim()+" "; }
		 */
		// TODO: revisar
		if (criteria.getCodeArea() != null) {
			query.append(" AND COD_AREA = :COD_AREA ");
			namedParameters.put("COD_AREA", criteria.getCodeArea());
		}

		if (criteria.getVigencia() != null) {
			query.append(" AND (vigencia = :vigencia "
					+ " OR sysdate > HASTA_VIGENCIA) ");
			namedParameters.put("vigencia", criteria.getVigencia());
		}

		query.append(" ORDER BY NOM_PROGRAMA");

		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				query.toString(), namedParameters, new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rst, int rowNum)
							throws SQLException {
						ProgramaVO pro = new ProgramaVO();
						pro.setCode(new Long(rst.getLong("CODIGO")));
						pro.setNombre(rst.getString("PROGRAMA"));
						pro.setCodeArea(new Long(rst.getString("CODAREA")));
						pro.setNombreArea(rst.getString("NombreAREA"));
						return pro;
					}
				});
		return programas;
	}

	public Collection<ProgramaVO> findProgramaWithoutVigencia(
			String groupUserName, Long codeUser, Long areaUser) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		StringBuffer query = new StringBuffer(
				"SELECT DISTINCT UPPER(NOM_PROGRAMA) Programa, COD_PROGRAMA Codigo, "
						+ " COD_AREA codArea, NOM_AREA nombreArea "
						+ " FROM VCS_BEN_SOLO_PROGRAMAS ");

		if (!groupUserName.equalsIgnoreCase("") && codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = :groupUserName "
					+ " OR 'SUPER USUARIO' = :groupUserName "
					+ "          OR COD_USUARIO = :codeUser ");
			namedParameters.put("groupUserName", groupUserName);
			namedParameters.put("codeUser", codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = :COD_AREA ");
				namedParameters.put("COD_AREA", areaUser);
			}
			query.append(" ) ");
		}
		query.append(" AND (vigencia = 'I' OR sysdate > HASTA_VIGENCIA) ORDER BY Programa");

		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				query.toString(), namedParameters, new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rst, int rowNum)
							throws SQLException {
						ProgramaVO pro = new ProgramaVO();
						pro.setCode(new Long(rst.getLong("CODIGO")));
						pro.setNombre(rst.getString("PROGRAMA"));
						pro.setCodeArea(new Long(rst.getString("CODAREA")));
						pro.setNombreArea(rst.getString("NombreAREA"));
						return pro;
					}
				});
		return programas;
	}

	public boolean EstadoPrograma(ProgramaVO criteria) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String query = "SELECT  codigo FROM tcs_ben_programa where  vigencia = 'I' AND codigo = :codigo ";

		namedParameters.put("codigo", criteria.getCodePrograma());
		try {
			getNamedParameterJdbcTemplate().queryForObject(query,
					namedParameters, String.class);
			return true;
		} catch (IncorrectResultSizeDataAccessException e) {
			// TODO: throw?
			return false;
		}

	}

	public int getActivarProg(ProgramaVO criteria) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String strSql = " UPDATE tcs_ben_programa SET vigencia = 'A' , comentario = '', hasta_vigencia=hasta_vigencia+TO_NUMBER(hasta_vigencia-desde_vigencia) "
				+ " WHERE codigo = :codigo ";

		namedParameters.put("codigo", criteria.getCodePrograma());

		return getNamedParameterJdbcTemplate().update(strSql, namedParameters);
	}

	public Collection<ProgramaVO> getProgramasVigentes(ProgramaVO criteria,
			String groupUserName, Long codeUser, Long areaUser,
			boolean controlAcceso) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		StringBuffer query = new StringBuffer(
				"SELECT  UPPER(NOM_PROGRAMA) Programa, COD_PROGRAMA Codigo, "
						+ " COD_AREA codArea, NOM_AREA nombreArea "
						+ " FROM VCS_BEN_SOLO_PROGRAMAS_EST_VIG ");

		if (!groupUserName.equalsIgnoreCase("") && codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = :groupUserName OR 'SUPER USUARIO' = :groupUserName "
					+ "          OR COD_USUARIO = :codeUser ");
			namedParameters.put("groupUserName", groupUserName);
			namedParameters.put("codeUser", codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = :COD_AREA ");
				namedParameters.put("COD_AREA", areaUser);
			}
			query.append(" ) ");
		}

		if (criteria != null) {
			if (criteria.getCodePrograma() != null
					&& !criteria.getCodePrograma().equalsIgnoreCase("")) {
				query.append(" AND COD_PROGRAMA = :COD_PROGRAMA ");
				namedParameters.put("COD_PROGRAMA", criteria.getCodePrograma()
						.trim());
			}
		}

		if (controlAcceso && areaUser != null && areaUser.longValue() != 0) {
			query.append(" AND COD_AREA = :COD_AREA ");
			namedParameters.put("COD_AREA", areaUser.longValue());
		}
		query.append(" ORDER BY NOM_PROGRAMA");

		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				query.toString(), namedParameters, new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rst, int rowNum)
							throws SQLException {
						ProgramaVO pro = new ProgramaVO();
						pro.setCodePrograma(rst.getString("CODIGO"));
						pro.setNombre(rst.getString("PROGRAMA"));
						pro.setCodeArea(new Long(rst.getString("CODAREA")));
						pro.setNombreArea(rst.getString("NombreAREA"));
						return pro;
					}
				});
		return programas;
	}

	public String getNameByCode(String strCodeProg) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String query = "SELECT nombre FROM tcs_ben_programa "
				+ " WHERE codigo = :codigo ";

		String strName = null;

		namedParameters.put("codigo", strCodeProg);

		try {
			strName = getNamedParameterJdbcTemplate().queryForObject(query,
					namedParameters, String.class);
		} catch (IncorrectResultSizeDataAccessException e) {
			// TODO: throw?
		}
		return strName;
	}

	public Collection<ProgramaVO> findProgramaByCriteriaWithGestor(
			ProgramaVO criteria, String groupUserName, Long codeUser,
			Long areaUser) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		StringBuffer query = new StringBuffer(
				"SELECT DISTINCT NOM_PROGRAMA Programa, COD_PROGRAMA Codigo, "
						+ " COD_AREA codArea, NOM_AREA nombreArea "
						+ " FROM VCS_BEN_SOLO_PROGRAMAS_EST_VIG, TCS_BEN_SUBPROGRAMA SUB, TCS_BEN_SUBP_ATR_BENEFICIARIO SAB ");

		if (!groupUserName.equalsIgnoreCase("") && codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = :groupUserName "
					+ " OR 'SUPER USUARIO' = :groupUserName "
					+ "          OR COD_USUARIO = :codeUser ");
			namedParameters.put("groupUserName", groupUserName);
			namedParameters.put("codeUser", codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = :COD_AREA ");
				namedParameters.put("COD_AREA", areaUser);
			}
			query.append(" ) ");
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND NOM_PROGRAMA LIKE :NOM_PROGRAMA ");
			namedParameters.put("NOM_PROGRAMA", "%"
					+ criteria.getNombre().toUpperCase().trim() + "%");
		}

		if (criteria.getCodePrograma() != null
				&& !criteria.getCodePrograma().equalsIgnoreCase("")) {
			query.append(" AND COD_PROGRAMA = :COD_PROGRAMA ");
			namedParameters.put("COD_PROGRAMA", criteria.getCodePrograma()
					.trim());
		}

		if (criteria.getCodeArea() != null) {
			query.append(" AND COD_AREA = :COD_AREA ");
			namedParameters.put("COD_AREA", criteria.getCodeArea());
		}

		if (criteria.getVigencia() != null) {
			query.append(" AND (vigencia = :vigencia "
					+ " OR sysdate > HASTA_VIGENCIA) ");
			namedParameters.put("vigencia", criteria.getVigencia());
		}

		query.append(" AND SUB.prg_codigo = COD_PROGRAMA AND SAB.SBPR_CODIGO = SUB.CODIGO AND UPPER(TRIM(SAB.NOMBRE)) = 'GESTOR' ");
		query.append(" ORDER BY NOM_PROGRAMA");

		List<ProgramaVO> programas = getNamedParameterJdbcTemplate().query(
				query.toString(), namedParameters, new RowMapper<ProgramaVO>() {

					public ProgramaVO mapRow(ResultSet rst, int rowNum)
							throws SQLException {
						ProgramaVO pro = new ProgramaVO();
						pro.setCode(new Long(rst.getLong("CODIGO")));
						pro.setNombre(rst.getString("PROGRAMA").toUpperCase());
						pro.setCodeArea(new Long(rst.getString("CODAREA")));
						pro.setNombreArea(rst.getString("NombreAREA"));
						return pro;
					}
				});
		return programas;
	}

	public int update(ProgramaVO vo) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String query = " UPDATE tcs_ben_programa SET area_codigo = :area_codigo, "
				+ " usu_codigo = :usu_codigo, "
				+ " nombre = UPPER(:nombre), "
				+ " descripcion = UPPER(:descripcion), "
				+ " objetivo = UPPER(:objetivo), "
				+ " poblacion_objetivo = UPPER(:poblacion_objetivo), "
				+ " desde_vigencia = TO_DATE(:desde_vigencia,'dd/mm/yyyy'), "
				+ " hasta_vigencia = TO_DATE(:hasta_vigencia,'dd/mm/yyyy'), "
				+ " vigencia = :vigencia, "
				+ " campo_A4 = :campo_A4, "
				+ " USUARIO_MODIFICA = :USUARIO_MODIFICA, FECHA_MODIFICA = TO_DATE(SYSDATE,'dd/mm/yyyy'), "
				+ " TERMINAL_MODIFICA = :TERMINAL_MODIFICA WHERE codigo = :codigo ";

		namedParameters.put("area_codigo", vo.getCodeArea());
		namedParameters.put("usu_codigo", vo.getCodeUsuario());
		namedParameters.put("nombre", vo.getNombre());
		namedParameters.put("descripcion", vo.getDescripcion());
		namedParameters.put("objetivo", vo.getObjetivo());
		namedParameters.put("poblacion_objetivo", vo.getPoblacionObjetivo());
		namedParameters.put("desde_vigencia", vo.getDesdeVigencia());
		namedParameters.put("hasta_vigencia", vo.getHastaVigencia());
		namedParameters.put("vigencia", vo.getVigencia());
		namedParameters.put("campo_A4", vo.getCampoA4());
		namedParameters.put("USUARIO_MODIFICA", vo.getUsuarioModifica());
		namedParameters.put("TERMINAL_MODIFICA", vo.getTerminalModifica());
		namedParameters.put("codigo", vo.getCode());
		/*
		 * System.out.println("query: " + query); for (String key :
		 * namedParameters.keySet()) { System.out.println(key + ":" +
		 * namedParameters.get(key)); }
		 */
		return getNamedParameterJdbcTemplate().update(query, namedParameters);
	}

	@Override
	public int findCantidadSubprogramas(Long codePrograma) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String query = "SELECT COUNT(sub.nombre) subprogramas "
				+ " FROM TCS_BEN_SUBPROGRAMA sub, TCS_BEN_PROGRAMA prog "
				+ " WHERE sub.PRG_CODIGO = prog.CODIGO AND prog.codigo = :prog.codigo";
		namedParameters.put("prog.codigo", codePrograma);

		try {
			Integer cantidad = getNamedParameterJdbcTemplate().queryForObject(
					query, namedParameters, Integer.class);
			return cantidad;
		} catch (IncorrectResultSizeDataAccessException e) {
			return 0;
		}
	}

	@Override
	public int inactivarPrograma(ProgramaVO programaVO) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String query = " UPDATE tcs_ben_programa SET vigencia ='I', "
				+ " COMENTARIO = :COMENTARIO, "
				+ " USUARIO_MODIFICA = :USUARIO_MODIFICA, "
				+ " FECHA_MODIFICA = TO_DATE(:FECHA_MODIFICA,'dd/mm/yyyy'), "
				+ " TERMINAL_MODIFICA = :TERMINAL_MODIFICA WHERE codigo = :CODIGO ";
		namedParameters.put("COMENTARIO", programaVO.getComentario());
		namedParameters
				.put("USUARIO_MODIFICA", programaVO.getUsuarioModifica());
		namedParameters.put("FECHA_MODIFICA", programaVO.getFechaModifica());
		namedParameters.put("TERMINAL_MODIFICA",
				programaVO.getTerminalModifica());
		namedParameters.put("CODIGO", programaVO.getCode());

		return getNamedParameterJdbcTemplate().update(query, namedParameters);
	}

	@Override
	public int insert(ProgramaVO programaVO) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String strSql = " INSERT INTO tcs_ben_programa (CODIGO,AREA_CODIGO, USU_CODIGO, "
				+ " NOMBRE, DESCRIPCION, OBJETIVO,POBLACION_OBJETIVO,DESDE_VIGENCIA, "
				+ " HASTA_VIGENCIA,VIGENCIA, USUARIO_CREA, FECHA_CREA, TERMINAL_CREA, "
				+ " USUARIO_MODIFICA, FECHA_MODIFICA, TERMINAL_MODIFICA, CAMPO_A4) "
				+ " values (SCS_BEN_PRG.NEXTVAL, :AREA_CODIGO, :USU_CODIGO, "
				+ " UPPER(:NOMBRE), UPPER(:DESCRIPCION), UPPER(:OBJETIVO), UPPER(:POBLACION_OBJETIVO), "
				+ " TO_DATE(:DESDE_VIGENCIA,'dd/mm/yyyy'), TO_DATE(:HASTA_VIGENCIA,'dd/mm/yyyy'), "
				+ " 'A', :USUARIO_CREA, TO_DATE(:FECHA_CREA,'dd/mm/yyyy'), :TERMINAL_CREA, "
				+ " :USUARIO_MODIFICA, TO_DATE(:FECHA_MODIFICA,'dd/mm/yyyy'), :TERMINAL_MODIFICA, :CAMPO_A4) ";

		namedParameters.put("AREA_CODIGO", programaVO.getCodeArea());
		namedParameters.put("USU_CODIGO", programaVO.getCodeUsuario());
		namedParameters.put("NOMBRE", programaVO.getNombre());
		namedParameters.put("DESCRIPCION", programaVO.getDescripcion());
		namedParameters.put("OBJETIVO", programaVO.getObjetivo());
		namedParameters.put("POBLACION_OBJETIVO",
				programaVO.getPoblacionObjetivo());
		namedParameters.put("DESDE_VIGENCIA", programaVO.getDesdeVigencia());
		namedParameters.put("HASTA_VIGENCIA", programaVO.getHastaVigencia());
		namedParameters.put("USUARIO_CREA", programaVO.getUsuarioCrea());
		namedParameters.put("FECHA_CREA", programaVO.getFechaCrea());
		namedParameters.put("TERMINAL_CREA", programaVO.getTerminalCrea());
		namedParameters
				.put("USUARIO_MODIFICA", programaVO.getUsuarioModifica());
		namedParameters.put("FECHA_MODIFICA", programaVO.getFechaModifica());
		namedParameters.put("TERMINAL_MODIFICA",
				programaVO.getTerminalModifica());
		namedParameters.put("CAMPO_A4", programaVO.getCampoA4());

		return getNamedParameterJdbcTemplate().update(strSql, namedParameters);
	}
}
